#!/usr/bin/env python
# -*- coding: UTF-8 -*-
"""
@Project ：scheduler.py 
@File ：物料价格分类赋值程序.py
@IDE  ：PyCharm 
@Author ：李涵彬
@Date ：2024/8/26 下午3:30 
"""

import xlrd
import re
import numpy as np
import tkinter as tk
from tkinter import filedialog, messagebox, Button, Label, Entry, StringVar
from typing import List, Dict, Optional
import pandas as pd
from pandas import DataFrame


# 定义读取Excel文件的函数
def read_excel(file_path: str) -> DataFrame | None:
	try:
		return pd.read_excel(file_path)
	except Exception as e:
		messagebox.showerror("错误", f"读取Excel文件时出错: {e}")
		return None


# 解析厚度组距和宽度组距字段
def parse_ranges(df: pd.DataFrame, col_names: list[str]):
	# 定义一个函数用于清理字符串
	def clean_string(s):
		# 移除括号和方括号
		s = re.sub(r'[\(\)\[\]]', '', s)
		return s

	# 定义一个函数用于将字符串转换为元组
	def string_to_tuple(s):
		try:
			# 尝试将字符串分割并转换为浮点数元组
			return tuple(map(float, s.split(',')))
		except ValueError:
			# 如果转换失败，返回NaN
			return np.nan

	for col_name in col_names:
		# 处理缺失值，使用np.nan代替空字符串
		df[col_name] = df[col_name].fillna(np.nan)

		# 清洗字符串
		df[col_name] = df[col_name].apply(clean_string)

		# 使用正则表达式匹配并转换为元组
		df[col_name] = df[col_name].apply(string_to_tuple)

	return df


# 物料价格分类赋值函数
def price_classification(classification_data: pd.DataFrame, detail_data: pd.DataFrame) -> pd.DataFrame:
	classification_data = classification_data.copy()
	detail_data = detail_data.copy()

	# 逐行遍历detail_data，如果存在匹配项，则将该分类的价格赋值给detail_data
	for index, row in detail_data.iterrows():
		# 获取当前行的物料规格
		leixing = row['成品卷类型']
		paihao = row['牌号']
		zhakuan = row['轧宽']
		zhahou = row['轧厚']

		for classification_index, classification_row in classification_data.iterrows():
			if paihao != classification_row['牌号']:
				continue
			else:
				if classification_row['宽度组距'][0] >= zhakuan or zhakuan > classification_row['宽度组距'][1]:
					continue
				else:
					if classification_row['厚度组距'][0] >= zhahou or zhahou > classification_row['厚度组距'][1]:
						continue
					else:
						detail_data.at[index, '加工费'] = classification_row[leixing]
						break
	return detail_data


def run(classification_data_path: str, detail_data_path: str) -> Optional[str]:
	try:
		# 读取物料规格分类表
		classification = read_excel(classification_data_path)
		# 读取物料详细信息表
		detail = read_excel(detail_data_path)

		if classification is None or detail is None:
			messagebox.showerror("错误", "读取Excel文件时出错")
			return None

		# 物料规格分类表数据预处理
		# 将“厚度组距”列和“宽度组距”列转换为元组类型，并去除空值和重复值
		classification = parse_ranges(classification, ['厚度组距', '宽度组距'])

		# 物料价格分类赋值
		result = price_classification(classification, detail)

		# 将结果在detail_data_path目录下保存为csv文件
		output_path = detail_data_path.replace('.xlsx', '_price_classification.xlsx')
		result.to_excel(output_path, index=False)

		return output_path

	except Exception as e:
		messagebox.showerror("错误", f"价格分类过程中出现错误：{str(e)}")
		return None


# 函数：选择人工计划Excel文件路径
def select_source_path():
	file_path = filedialog.askopenfilename(
		title="选择物料规格分类Excel文件",
		filetypes=(("Excel files", "*.xlsx"), ("all files", "*.*"))
	)
	if file_path:
		source_path_var.set(file_path)
		source_path_label.config(text="选择物料规格分类Excel文件路径: " + file_path)


def select_target_path():
	file_path = filedialog.askopenfilename(
		title="选择物料信息Excel文件",
		filetypes=(("Excel files", "*.xlsx"), ("all files", "*.*"))
	)
	if file_path:
		target_path_var.set(file_path)
		target_path_label.config(text="选择物料信息Excel文件路径: " + file_path)


# 函数：执行价格分类
def run_price_classification():
	output_path = run(source_path_var.get(), target_path_var.get())

	if output_path:
		messagebox.showinfo("完成", f"比对结果已保存至: {output_path}")


# 主函数
if __name__ == '__main__':
	# 创建主窗口
	root = tk.Tk()
	root.title("物料价格分类工具")

	# 创建路径字符串变量
	source_path_var = StringVar(root)
	target_path_var = StringVar(root)

	# 创建人工计划选择按钮和标签
	source_path_label = Label(root, text="物料规格分类Excel文件: 未选择")
	source_path_label.pack()

	source_path_choose_button = Button(root, text="选择物料规格分类Excel文件", command=select_source_path)
	source_path_choose_button.pack()

	# 创建目标文件目录选择按钮和标签
	target_path_label = Label(root, text="物料信息Excel文件路径: 未选择")
	target_path_label.pack()

	target_path_choose_button = Button(root, text="选择物料信息Excel文件", command=select_target_path)
	target_path_choose_button.pack()

	# 创建执行比对按钮
	compare_button = Button(root, text="执行价格分类", command=run_price_classification)
	compare_button.pack()

	# 运行主循环
	root.mainloop()
